﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAO
{
    public class KhachHangDAO
    {
        QLThuVienDataContext dt = new QLThuVienDataContext();
        public int KiemTraLogin(string p1, string p2)
        {
             var dgInfo = dt.DOCGIAs.Where(dg => dg.MA_DOC_GIA == p1 && dg.MAT_KHAU == p2).SingleOrDefault();
             if (dgInfo != null)
             {
                 return 1;
             }
             else {
                 return 2;
             }
        }



        public DOCGIA LayThongTinDocGia(string p1, string p2)
        {
            
           
            DOCGIA dg = new DOCGIA();
            dg = (from a in dt.DOCGIAs
                  where a.MA_DOC_GIA == p1 && a.MAT_KHAU == p2
                  select a).Single();
               
       
            return dg;
        }

        public IQueryable loaddocgia(string ma)
        {
            var ds = (from a in dt.DOCGIAs 
                      join p in dt.LOAIDOCGIAs on a.LOAI_DOC_GIA equals p.MA_LOAI 
                      where a.MA_DOC_GIA == ma.Trim()
                      select new { MA_DOC_GIA = a.MA_DOC_GIA, TEN_DOC_GIA = a.TEN_DOC_GIA, SDT = a.SDT, DIA_CHI = a.DIA_CHI, NGAY_SINH = a.NGAY_SINH, NGAY_LAP_THE = a.NGAY_LAP_THE, NGAY_HET_HAN = a.NGAY_HET_HAN, LOAI_DOC_GIA =p.TEN_LOAI, NGUOI_LAP = a.NHANVIEN.TEN_NHAN_VIEN });
              return ds;
        }
        public Boolean CapNhatMatKhau(DOCGIA dg, string mkm)
        {
           
            var acc = (from a in dt.DOCGIAs where a.MA_DOC_GIA == dg.MA_DOC_GIA select a).SingleOrDefault();
            if (acc == null)
            {
                return false;
            }
            acc.MAT_KHAU = mkm;
            dt.SubmitChanges();
            return true;
        }
        public IQueryable loadphieumuondocgia(string ma)
        {
           // List<PHIEUMUON> l = new List<PHIEUMUON>();
            var l = (from a in dt.PHIEUMUONs
                     where a.MA_DOC_GIA == ma
                     select new { MA_PHIEU = a.MA_PHIEU, MA_DOC_GIA = a.MA_DOC_GIA, NGAY_MUON = a.NGAY_MUON });
            return l;
        }
        public IQueryable loadphieumuon()
        {
            // List<PHIEUMUON> l = new List<PHIEUMUON>();
            var l = (from a in dt.PHIEUMUONs
                     
                     select new { MA_PHIEU = a.MA_PHIEU, MA_DOC_GIA = a.MA_DOC_GIA, NGAY_MUON = a.NGAY_MUON });
            return l;
        }

        public IQueryable loadphieutradocgia(string ma)
        {
           // List<PHIEUMUON> l = new List<PHIEUMUON>();
            var l = (from a in dt.PHIEUTRAs
                     where a.MA_DOC_GIA == ma
                     select new { MA_PHIEU = a.MA_PHIEU, MA_DOC_GIA = a.MA_DOC_GIA, NGAY_MUON = a.NGAY_MUON, TIEN_PHAT = a.TIEN_PHAT });
            return l;
        }
        public IQueryable loadchitietphieu(int ma)
        {
            // List<PHIEUMUON> l = new List<PHIEUMUON>();
            var l = (from a in dt.CHITIETPHIEUMUONs
                     join s in dt.SACHes on a.MA_SACH equals s.MA_SACH
                     where a.MA_PHIEU_MUON == ma
                     select new { TEN_SACH = s.TEN_SACH, TAC_GIA = s.TAC_GIA, NHA_XB = s.NHA_XB, GIA_SACH = s.GIA_SACH, MA_CHI_TIET = a.MA_CHI_TIET });
            return l;
        }
        public IQueryable loadchitietphieutra(int ma)
        {
            // List<PHIEUMUON> l = new List<PHIEUMUON>();
            var l = (from a in dt.CHITIETPHIEUTRAs
                     join s in dt.SACHes on a.MA_SACH equals s.MA_SACH
                     where a.MA_PHIEU_TRA == ma
                     select new { TEN_SACH = a.SACH.TEN_SACH ,TAC_GIA = s.TAC_GIA, NHA_XB = s.NHA_XB, GIA_SACH = s.GIA_SACH });
            return l;
        }
        public IQueryable timkiemsach(string ten, string tacgia, int maloai)
        {
            //var ds = (from a in dt.SACHes
            //               where a.TEN_SACH == ten && a.TAC_GIA == tacgia && a.THE_LOAI == maloai
            //               select new { MA_SACH = a.MA_SACH, TEN_SACH = a.TEN_SACH, THE_LOAI = a.LOAISACH.TEN_LOAI, TAC_GIA = a.TAC_GIA, TINH_TRANG = a.TINH_TRANG, NAMXB = a.NAMXB, NHA_XB = a.NHA_XB, NGAY_NHAP = a.NGAY_NHAP, GIA_SACH = a.GIA_SACH, NGUOI_TIEP_NHAN = a.NHANVIEN.TEN_NHAN_VIEN });
            if ( (ten !="") && (tacgia !=""))
            {
                var ds = (from a in dt.SACHes
                          where a.TEN_SACH == ten && a.TAC_GIA == tacgia && a.THE_LOAI == maloai
                          select new { MA_SACH = a.MA_SACH, TEN_SACH = a.TEN_SACH, THE_LOAI = a.LOAISACH.TEN_LOAI, TAC_GIA = a.TAC_GIA, TINH_TRANG = a.TINH_TRANG, NAMXB = a.NAMXB, NHA_XB = a.NHA_XB, NGAY_NHAP = a.NGAY_NHAP, GIA_SACH = a.GIA_SACH, NGUOI_TIEP_NHAN = a.NHANVIEN.TEN_NHAN_VIEN });
                return ds;
            }
            else if ((ten == "") && (tacgia != ""))
            {
                var ds = (from a in dt.SACHes
                          where  a.TAC_GIA == tacgia && a.THE_LOAI == maloai
                          select new { MA_SACH = a.MA_SACH, TEN_SACH = a.TEN_SACH, THE_LOAI = a.LOAISACH.TEN_LOAI, TAC_GIA = a.TAC_GIA, TINH_TRANG = a.TINH_TRANG, NAMXB = a.NAMXB, NHA_XB = a.NHA_XB, NGAY_NHAP = a.NGAY_NHAP, GIA_SACH = a.GIA_SACH, NGUOI_TIEP_NHAN = a.NHANVIEN.TEN_NHAN_VIEN });
                return ds;
            }
            else if ((ten != "") && (tacgia == ""))
            {
                var ds = (from a in dt.SACHes
                          where a.TEN_SACH == ten && a.THE_LOAI == maloai
                          select new { MA_SACH = a.MA_SACH, TEN_SACH = a.TEN_SACH, THE_LOAI = a.LOAISACH.TEN_LOAI, TAC_GIA = a.TAC_GIA, TINH_TRANG = a.TINH_TRANG, NAMXB = a.NAMXB, NHA_XB = a.NHA_XB, NGAY_NHAP = a.NGAY_NHAP, GIA_SACH = a.GIA_SACH, NGUOI_TIEP_NHAN = a.NHANVIEN.TEN_NHAN_VIEN });
                return ds;
            }
            else
            {
                var ds = (from a in dt.SACHes
                          where a.THE_LOAI == maloai
                          select new { MA_SACH = a.MA_SACH, TEN_SACH = a.TEN_SACH, THE_LOAI = a.LOAISACH.TEN_LOAI, TAC_GIA = a.TAC_GIA, TINH_TRANG = a.TINH_TRANG, NHA_XB = a.NHA_XB, NGAY_NHAP = a.NGAY_NHAP, GIA_SACH = a.GIA_SACH, NGUOI_TIEP_NHAN = a.NHANVIEN.TEN_NHAN_VIEN });
                return ds;
            }
        
        }
        public IQueryable timkiemsach2(string ten)
        {
            var ds = (from a in dt.SACHes
                      where a.TEN_SACH == ten 
                      select new { MA_SACH = a.MA_SACH, TEN_SACH = a.TEN_SACH, THE_LOAI = a.LOAISACH.TEN_LOAI, TAC_GIA = a.TAC_GIA, TINH_TRANG = a.TINH_TRANG, NHA_XB = a.NHA_XB, NGAY_NHAP = a.NGAY_NHAP, GIA_SACH = a.GIA_SACH, });
            return ds;
            
        }
        public List<LOAISACH> loadloaisach()
        {
            List<LOAISACH> l = new List<LOAISACH>();
            l = dt.LOAISACHes.ToList();
            return l;
        }

        public object loadphieutra()
        {
            var l = (from a in dt.PHIEUTRAs

                     select new { MA_PHIEU = a.MA_PHIEU, MA_DOC_GIA = a.MA_DOC_GIA, NGAY_MUON = a.NGAY_MUON, TIEN_PHAT = a.TIEN_PHAT });
            return l;
        }

        public object timkiemsachpt(string ten)
        {
            var ds = (from a in dt.SACHes
                      where a.TEN_SACH.Contains(ten)
                      select new { MA_SACH = a.MA_SACH, TEN_SACH = a.TEN_SACH, THE_LOAI = a.LOAISACH.TEN_LOAI, TAC_GIA = a.TAC_GIA, TINH_TRANG = a.TINH_TRANG, NHA_XB = a.NHA_XB, NGAY_NHAP = a.NGAY_NHAP, GIA_SACH = a.GIA_SACH, });
            return ds;
        }
    }
}
